package com.bj58.ecdata.mailreport.service.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bj58.ecdata.mailreport.db.CommonDao;
import com.bj58.ecdata.mailreport.db.DBConfig;
import com.bj58.ecdata.mailreport.model.ErShouXiaoshou;
import com.bj58.ecdata.mailreport.model.JianliReportKpi;
import com.bj58.ecdata.mailreport.service.EmailReportService;
import com.bj58.ecdata.mailreport.util.CommonDataDeal;
import com.bj58.ecdata.mailreport.util.DateUtil;

public class EmailReportServiceImpl implements EmailReportService {
	private CommonDao comDao = new CommonDao();
	private CommonDataDeal cdd = new CommonDataDeal();
	
	@Override
	public JianliReportKpi getJLKpiData(String nowDate) throws Exception{
		JianliReportKpi jlReportKpi = new JianliReportKpi();
		String month = nowDate.substring(0,nowDate.lastIndexOf("-"));
			StringBuffer sql = new StringBuffer();
			//kpi目标
			sql.append(" SELECT kpi FROM `jianli_report_kpi`");
			sql.append(" where month = '"+month+"'");
			List<String> cols = new ArrayList<String>();
			cols.add("kpi");
			List<JianliReportKpi> kpiList = comDao.getCommonStatList(sql.toString(), cols, JianliReportKpi.class, DBConfig.db_manage.getIntType());
			if(null == kpiList ||  kpiList.isEmpty() || kpiList.size() != 1){
				return null;
			}
			jlReportKpi.setKpi(kpiList.get(0).getKpi());
			//月累计摊销
			sql.delete(0, sql.length()+1);
			cols.clear();
			cols.add("monthSumIncome");
			List<JianliReportKpi> monthSumIncome  = new ArrayList<JianliReportKpi>();
			sql.append(" select sum(dayAmortize)");
			sql.append(" from amortize_metrics");
			sql.append(" where vDate>='"+nowDate.substring(0,nowDate.lastIndexOf("-"))+"-01' and vDate<='"+nowDate+"' ");
			sql.append("  and memberId in(1097,1104,1038)");
			monthSumIncome = comDao.getCommonStatList(sql.toString(), cols, JianliReportKpi.class, DBConfig.db_online_revenue.getIntType());
			if(null == monthSumIncome ||  monthSumIncome.isEmpty()){
				return null;
			}
			jlReportKpi.setMonthSumIncome(monthSumIncome.get(0).getMonthSumIncome());
			
			String monthFirstDay = DateUtil.getFirstDay(DateUtil.strToDate(nowDate, "yyyy-MM-dd"));	//当月第一天
			int monthDaysNum = DateUtil.getMonthDays(nowDate,"yyyy-MM-dd"); //当月总天数
			long hasPasDays = DateUtil.getDistDates(DateUtil.strToDate(monthFirstDay, "yyyy-MM-dd"), DateUtil.strToDate(nowDate, "yyyy-MM-dd")) +1;//已过去的天数
			long subDay  = monthDaysNum - hasPasDays; //剩余天数
			double tragetProgress = (double)hasPasDays/monthDaysNum;
			
			jlReportKpi.setTragetProgress(tragetProgress);
			jlReportKpi.setHasDoneProgress((double)jlReportKpi.getMonthSumIncome()/jlReportKpi.getKpi());
			if(subDay > 0){
				jlReportKpi.setLaveDayIncome( (jlReportKpi.getKpi() - jlReportKpi.getMonthSumIncome())/subDay );
			}else{
				jlReportKpi.setLaveDayIncome(0);
			}
		return jlReportKpi;
	}

	@Override
	public Map<String,ErShouXiaoshou> getErshouXSDayData(String date) throws Exception {
		String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
		StringBuffer sql = new StringBuffer();
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		String cols1[]={"stat_date","incomeType","laveDayIncome"};
		List<String> colsList1=Arrays.asList(cols1);
		String firstDay = DateUtil.firstDayMonthBefore(date, "yyyy-MM-dd", 0);
		
		sql.append(" select create_date,case when order_type=1000 then '新签' else '续费' end,sum(first_payment) as '金额'  ");
		sql.append(" from t_common_order_info");
		sql.append(" where order_type in(1000,1005)");
		sql.append(" AND first_payment != 0 AND order_state IN (45,47) ");
		sql.append(" AND product_id IN (100601, 100101,100401) ");
		sql.append(" AND date_format(service_begin_time,'%Y-%m')=date_format('"+date+"','%Y-%m') ");
		sql.append(" AND (disp_cate1_name IN ('二手市场', '票务') ");
		sql.append(" OR (mini_cate2_name = '二手回收' AND disp_city1_name IN ('北京', '成都', '大连', '东莞', '福州', '广州', '哈尔滨', '杭州', '合肥', '济南', '南京', '南宁', '青岛', '厦门', '上海', '深圳', '沈阳', '石家庄', '苏州', '太原', '天津', '无锡', '武汉', '长春', '长沙', '郑州', '重庆'))");
		sql.append(" OR mini_cate2_name IN ('二手摩托车', '自行车/电动车','机票','宠物狗','宠物猫','花鸟鱼虫'))");
		sql.append(" and create_date BETWEEN '"+firstDay+"' and '"+date+"'");
		sql.append(" group by 1,order_type :"+DBConfig.db_data_app_94.getIntType());
		sqlMap.put(sql.toString(),colsList1);
		
		sql.delete(0, sql.length()+1);
		sql.append("SELECT tb1.stat_date, case when tb1.stat_date=tb2.stat_date then '充值' end,(tb1.sum_money-tb2.sum_money) as '充值金额'");
		sql.append(" FROM (");
		sql.append(" 	SELECT a.stat_date as 'stat_date',sum(if(b.order_state=45 or (b.order_state=50 and date_format(service_end_time,'%Y-%m-%d')=date_format(a.stat_date,'%Y-%m-%d')),a.pay_money,0)) sum_money");
		sql.append(" 	FROM m_pay_v3_bid_cash_flow_"+month+" a LEFT JOIN  m_common_order_info b on a.user_id=b.user_id");
		sql.append(" 	WHERE b.order_state in(45,50)");
		sql.append(" 	AND b.product_id IN (100601, 100101,100401) AND");
		sql.append("   (b.disp_cate1_name IN ('二手市场', '票务') OR ( b.mini_cate2_name = '二手回收' AND b.disp_city1_name IN ('北京', '成都', '大连', '东莞', '福州', '广州', '哈尔滨', '杭州', '合肥', '济南', '南京', '南宁', '青岛', '厦门', '上海', '深圳', '沈阳', '石家庄', '苏州', '太原', '天津', '无锡', '武汉', '长春', '长沙', '郑州', '重庆')");
		sql.append("    )OR b.mini_cate2_name IN ('二手摩托车', '自行车/电动车','机票','宠物狗','宠物猫','花鸟鱼虫') ) ");
		sql.append(" 	AND a.stat_date >= '"+firstDay+"' AND buss_type in ('1','7','23') group by 1");
		sql.append(" ) tb1 left join (");
		sql.append(" 	SELECT a.stat_date as 'stat_date',sum(if(b.order_state=45 or (b.order_state=50 and date_format(service_end_time,'%Y-%m-%d')=date_format(a.stat_date,'%Y-%m-%d')),a.pay_money,0)) sum_money");
		sql.append(" 	FROM m_pay_v3_bid_cash_flow_"+month+" a LEFT JOIN  m_common_order_info b on a.user_id=b.user_id");
		sql.append(" 	WHERE b.order_state in(45,50)");
		sql.append(" 	AND b.product_id IN (100601, 100101,100401) AND");
		sql.append("   (b.disp_cate1_name IN ('二手市场', '票务') OR ( b.mini_cate2_name = '二手回收' AND b.disp_city1_name IN ('北京', '成都', '大连', '东莞', '福州', '广州', '哈尔滨', '杭州', '合肥', '济南', '南京', '南宁', '青岛', '厦门', '上海', '深圳', '沈阳', '石家庄', '苏州', '太原', '天津', '无锡', '武汉', '长春', '长沙', '郑州', '重庆')");
		sql.append("    )OR b.mini_cate2_name IN ('二手摩托车', '自行车/电动车','机票','宠物狗','宠物猫','花鸟鱼虫') ) ");
		sql.append(" 	AND a.stat_date >= '"+firstDay+"' AND buss_type in ('4','6','22') group by 1");
		sql.append(") tb2 on tb1.stat_date=tb2.stat_date");
		sql.append(" :"+DBConfig.db_hive_middle.getIntType());
		sqlMap.put(sql.toString(),colsList1);
		
		sql.delete(0, sql.length()+1);
		sql.append(" select stat_date,case when stat_date>'2000-01-01' then '精准' end,sum(cash)/100 as '消耗'");
		sql.append(" from t_jingzhun_city_cate_stat_new_"+month);
		sql.append(" where");
		sql.append(" stat_date <= '"+date+"' and (( disp_cate1_name IN ( '二手市场', '宠物', '票务' )) OR ( disp_cate1_name = '车辆买卖与服务' AND disp_cate2_name IN ( '二手摩托车', '自行车/电动车', '拼车/顺风车' )) OR ( disp_cate2_name = '二手回收' AND disp_city1_name IN ( '北京', '成都', '重庆', '石家庄', '济南', '青岛', '南京', '杭州', '苏州', '无锡', '合肥', '武汉', '郑州', '长沙', '福州', '厦门', '南宁', '哈尔滨', '长春', '太原', '大连', '沈阳', '上海', '天津', '广州', '深圳', '东莞' ))) GROUP BY 1");
		sql.append(" :"+DBConfig.db_data_app_94.getIntType());
		sqlMap.put(sql.toString(),colsList1);
		
		sql.delete(0, sql.length()+1);
		sql.append(" select stat_date,case when stat_date>'2000-01-01' then '置顶' end,sum(cash)/100 as '消耗'");
		sql.append(" from t_zhiding_city_cate_stat_new_"+month);
		sql.append(" where");
		sql.append(" stat_date <= '"+date+"' and (( disp_cate1_name IN ( '二手市场', '宠物', '票务' )) OR ( disp_cate1_name = '车辆买卖与服务' AND disp_cate2_name IN ( '二手摩托车', '自行车/电动车', '拼车/顺风车' )) OR ( disp_cate2_name = '二手回收' AND disp_city1_name IN ( '北京', '成都', '重庆', '石家庄', '济南', '青岛', '南京', '杭州', '苏州', '无锡', '合肥', '武汉', '郑州', '长沙', '福州', '厦门', '南宁', '哈尔滨', '长春', '太原', '大连', '沈阳', '上海', '天津', '广州', '深圳', '东莞' ))) GROUP BY 1");
		sql.append(" :"+DBConfig.db_data_app_94.getIntType());
		sqlMap.put(sql.toString(),colsList1);

		sql.delete(0, sql.length()+1);
		sql.append(" select stat_date,case when stat_date>'2000-01-01' then '智能' end,sum(cash)/100 as '消耗'");
		sql.append(" from t_zhineng_city_cate_stat_new_"+month);
		sql.append(" where");
		sql.append(" stat_date <= '"+date+"' and (( disp_cate1_name IN ( '二手市场', '宠物', '票务' )) OR ( disp_cate1_name = '车辆买卖与服务' AND disp_cate2_name IN ( '二手摩托车', '自行车/电动车', '拼车/顺风车' )) OR ( disp_cate2_name = '二手回收' AND disp_city1_name IN ( '北京', '成都', '重庆', '石家庄', '济南', '青岛', '南京', '杭州', '苏州', '无锡', '合肥', '武汉', '郑州', '长沙', '福州', '厦门', '南宁', '哈尔滨', '长春', '太原', '大连', '沈阳', '上海', '天津', '广州', '深圳', '东莞' ))) GROUP BY 1");
		sql.append(" :"+DBConfig.db_data_app_94.getIntType());
		sqlMap.put(sql.toString(),colsList1);
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("stat_date");gbList.add("incomeType");
		
		// <"日期,收入类型" : vo >
		Map<String, ErShouXiaoshou> date_typeMap = cdd.getDataMapBySqlMap(sqlMap, ErShouXiaoshou.class, gbList);

		String t1[] = {"新签","续费","充值"};
		String t2[] = {"精准","置顶","智能"};
		
		List<String> dateList = DateUtil.getDateList(DateUtil.firstDayMonthBefore(date, "yyyy-MM-dd", 0), date);
		
		double refoundTotal=0;
		ErShouXiaoshou refoundVo = new ErShouXiaoshou(null,"回款总计");
		double consumTotal=0;
		ErShouXiaoshou consumVo = new ErShouXiaoshou(null,"消耗总计");
		
		//算每天的总计,同时算每个类型的汇总
		for(String day : dateList){
			double dayTotal=0;
			//回款
			for(String s : t1){
				double income=0;
				ErShouXiaoshou vo = date_typeMap.get(day+","+s);
				if(vo!=null){
					dayTotal+=vo.getLaveDayIncome(); //天维度累加
					income = vo.getLaveDayIncome();
				}
				
				ErShouXiaoshou tmpVo =  date_typeMap.get(s);
				if(tmpVo == null){
					tmpVo = new ErShouXiaoshou(null,s);
					tmpVo.setIncomeType(s);
				}
				tmpVo.setLaveDayIncome(tmpVo.getLaveDayIncome()+income); //本月收入类型维度sum累加
				date_typeMap.put(s, tmpVo);
			}
			ErShouXiaoshou dayTotalVo = new ErShouXiaoshou(day,"回款总计");	//当天
			dayTotalVo.setLaveDayIncome(dayTotal);
			date_typeMap.put(day+",回款总计", dayTotalVo);
			refoundTotal += dayTotal;
			
			//消耗
			dayTotal=0;
			for(String s : t2){
				double income=0;
				ErShouXiaoshou vo = date_typeMap.get(day+","+s);
				if(vo!=null){
					dayTotal+=vo.getLaveDayIncome(); //天维度累加
					income = vo.getLaveDayIncome();
				}
				
				ErShouXiaoshou tmpVo =  date_typeMap.get(s);
				if(tmpVo == null){
					tmpVo = new ErShouXiaoshou(null,s);
					tmpVo.setIncomeType(s);
				}
				tmpVo.setLaveDayIncome(tmpVo.getLaveDayIncome()+income); //收入类型维度累加
				date_typeMap.put(s, tmpVo);
			}
			dayTotalVo = new ErShouXiaoshou(day,"消耗总计"); //当天
			dayTotalVo.setLaveDayIncome(dayTotal);
			date_typeMap.put(day+",消耗总计", dayTotalVo);
			consumTotal += dayTotal;
		}
		refoundVo.setLaveDayIncome(refoundTotal);
		consumVo.setLaveDayIncome(consumTotal);
		date_typeMap.put("回款总计", refoundVo); //本月至今
		date_typeMap.put("消耗总计", consumVo); //本月至今
		
		return date_typeMap;

	}

	@Override
	public Map<String, ErShouXiaoshou> getErshouXSRefoundData(String date, Map<String, ErShouXiaoshou> daydata) throws Exception {
		String month = DateUtil.getMonth(date, "yyyy-MM-dd", "yyyy年M月");
		StringBuffer sql = new StringBuffer();
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		String cols1[]={"incomeType","kpi"};
		List<String> colsList1=Arrays.asList(cols1);
//		String firstDay = DateUtil.firstDayMonthBefore(date, "yyyy-MM-dd", 0);
		
		sql.append(" select case when stat_date = '"+month+"' then '新签' else '新签' end,SUM(new_task) as '任务金额' ");
		sql.append(" from t_salesman_task");
		sql.append(" WHERE stat_date = '"+month+"'");
		sql.append(" group by 1 :"+DBConfig.db_data_ershou_133.getIntType());
		sqlMap.put(sql.toString(),colsList1);
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("incomeType");
		
		// <"日期,收入类型" : vo >
		Map<String, ErShouXiaoshou> typeMap = cdd.getDataMapBySqlMap(sqlMap, ErShouXiaoshou.class, gbList);
		int monthDays = DateUtil.getMonthDays(date,"yyyy-MM-dd"); //当月总天数
		int dayOfMonth =  DateUtil.getDayOfMonth(date,"yyyy-MM-dd");
		String monthPrefix = date.substring(0,date.lastIndexOf("-")); 	// "2015-04"
		double timeProcess = dayOfMonth*1.0/monthDays;
		
		String t1[] = {"新签"};
		for(String s : t1){
			ErShouXiaoshou vo = daydata.get(s);
			vo.setTragetProgress(timeProcess);	//时间进度
			ErShouXiaoshou kpiVo = typeMap.get(s);
			vo.setKpi( kpiVo.getKpi());		//任务
			double hasDone = vo.getLaveDayIncome();
			double kpi = kpiVo.getKpi();
			vo.setHasDoneProgress(hasDone/kpi);	//完成进度
			
			String startDate = monthPrefix+"-01";
			String endDate = date;
			double x = 0; //风险系数
			double eachDayShouldDone = 0; 
			if(dayOfMonth <10){	//上旬
				eachDayShouldDone = (kpi*1/4.5 - hasDone)/(10-dayOfMonth);
			}else if(dayOfMonth >=10 && dayOfMonth < 20){	//中旬
				eachDayShouldDone =(kpi*2.5/4.5 - hasDone)/(20-dayOfMonth);
				if(dayOfMonth == 10){
					startDate = monthPrefix+"-01";
				}else{
					startDate = monthPrefix+"-11";
				}
			}else if(dayOfMonth >=20 && dayOfMonth < monthDays){	//下旬
				eachDayShouldDone =(kpi*2.5/4.5 - hasDone)/(monthDays-dayOfMonth);
				if(dayOfMonth == 20){
					startDate = monthPrefix+"-11";
				}else{
					startDate = monthPrefix+"-21";
				}
			}
			
			List<String> dateList = DateUtil.getDateList(startDate, endDate);
			double lastDaysAVG = avgIncome(daydata,s,dateList);
			
			if(dayOfMonth == monthDays){ //月末
				x=0;
			}else{
				x = eachDayShouldDone/lastDaysAVG;
			}
			if(x < 0)	
				x=0;
			vo.setRiskRate(x);
			daydata.put(s, vo);
		};
		return daydata;
	}
	
	private double avgIncome(Map<String, ErShouXiaoshou> daydata,String incomeType,List<String> dateList){
		double sum=0;
		//算每天的总计,同时算每个类型的汇总
		for(String day : dateList){
			ErShouXiaoshou vo = daydata.get(day+","+incomeType);
			if(vo!=null){
				sum+=vo.getLaveDayIncome(); //累加
			}
		}
		
		return sum/dateList.size();
	}
}
